package com.kaizolo.gilabert.admin;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;

import org.apache.log4j.Logger;
import org.xml.sax.SAXException;

import com.kaizolo.gilabert.admin.managers.UsuarioMgr;
import com.kaizolo.gilabert.admin.XMLUsuario.Usuario;
import com.kaizolo.gilabert.utils.*;

public class ServletUsuario extends HttpServlet {
	
	private static String TABLA_USUARIO = "usuarios";
	
	private static Logger logger = Logger.getLogger(ServletUsuario.class);

	/**
	 * Constructor of the object.
	 */
	public ServletUsuario() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doPost(request,response);
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

         String xml=new String();
         PrintWriter out=null;
         
         /**
          * Obtenemos el xml del post
          */                 
         xml = request.getParameter("xml"); 
         xml = new String(xml.getBytes(),"UTF-8");
         
         logger.info("---");
         logger.info("Peticion Usuario: "+xml);
         logger.info("---");
         
         String respuesta = null;
         
         try {
        	 Usuario usuario = validaPeticion(xml);
        	 DBOperations.getInstance();
        	 ArrayList returned = new ArrayList();
             if (usuario.getOperacion().compareToIgnoreCase("list") == 0) {
            	 String sql = "SELECT count(*) as numregs FROM " + TABLA_USUARIO;
            	 returned = DBOperations.executeQuery(sql);
            	 ResultSet rst = (ResultSet) returned.get(1);
            	 rst.next();
            	 Integer numregs = rst.getInt("numregs");
            	 logger.info("Numregs Usuario: "+numregs);
                 logger.info("---");
                 DBOperations.closeConnection(returned);
            	 
            	 sql = "SELECT id, nombre, login, email FROM " + TABLA_USUARIO;            
            	 if(request.getParameter("sort") != null)
            		 sql += " ORDER BY " + request.getParameter("sort") + " " + request.getParameter("dir");
            	 else sql += " ORDER BY id";
            	 sql += " LIMIT "+request.getParameter("limit")+" OFFSET " + request.getParameter("start");
                 returned = DBOperations.executeQuery(sql);
                 rst = (ResultSet) returned.get(1);
                 
                 logger.info("Consulta Usuarios: "+sql);
                 logger.info("---");
            	 
            	 respuesta = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><list><numregs>"+numregs+"</numregs>";
            	 while (rst.next()){
            		 respuesta += "<usuario><id>"+rst.getInt("id")+"</id>";
            		 respuesta += "<nombre>"+DBOperations.getFieldValueAsString(rst, "nombre")+"</nombre>";
            		 respuesta += "<login>"+DBOperations.getFieldValueAsString(rst, "login")+"</login>";
            		 respuesta += "<email>"+DBOperations.getFieldValueAsString(rst, "email")+"</email></usuario>";
            	 }
            	 respuesta += "</list>";
            	 DBOperations.closeConnection(returned);
            	 
			}else{
				if (usuario.getOperacion().compareToIgnoreCase("new") == 0) {
					UsuarioMgr umgr = new UsuarioMgr();
					String sql = "INSERT INTO " + TABLA_USUARIO + "(id,nombre,login,password) VALUES ("+umgr.getIdLibre()+",'"+usuario.getNombre()+"','"+usuario.getLogin()+"','"+usuario.getPassword()+"')";
					logger.info("SQL ="+sql);
					DBOperations.executeInsert(sql);
					respuesta = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><new>ok</new>";
					respuesta = "{\"success\":\"true\"}";
					logger.info(respuesta);					
				}else{
					if (usuario.getOperacion().compareToIgnoreCase("update") == 0) {
						String sql = "UPDATE " + TABLA_USUARIO + " SET nombre='"+usuario.getNombre()+"' ,login='"+usuario.getLogin()+"' ,";
						if(usuario.getPassword() != null)
							sql += "password='"+usuario.getPassword()+"', "; 
						sql += "email='"+usuario.getEmail()+"' WHERE id="+usuario.getId();
						DBOperations.executeUpdate(sql);
						respuesta = "{\"success\":\"true\"}";
					}else{
						if (usuario.getOperacion().compareToIgnoreCase("show") == 0) {							          
			            	 returned = DBOperations.executeQuery("SELECT * FROM " + TABLA_USUARIO + " WHERE id="+usuario.getId());
			            	 ResultSet rst = (ResultSet) returned.get(1);
			            	 respuesta = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><show>";
			            	 while (rst.next()){			            		
			            		 respuesta += "<usuario><id>"+rst.getInt("id")+"</id>";
			            		 respuesta += "<nombre>"+DBOperations.getFieldValueAsString(rst, "nombre")+"</nombre>";
			            		 respuesta += "<login>"+DBOperations.getFieldValueAsString(rst, "login")+"</login>";
			            		 respuesta += "<password>"+DBOperations.getFieldValueAsString(rst, "password")+"</password>";
			            		 respuesta += "<email>"+DBOperations.getFieldValueAsString(rst, "email")+"</email></usuario>";			            		 			            		
			            	 }
			            	 respuesta += "</show>";			            	
			            	 DBOperations.closeConnection(returned);
						}else{
							if (usuario.getOperacion().compareToIgnoreCase("delete") == 0) {
								String sql = "DELETE FROM " + TABLA_USUARIO + " WHERE id="+usuario.getId();
								DBOperations.executeUpdate(sql);											
								respuesta = "{\"success\":\"true\"}";
							}
						}
					}
				}
			}
          } catch (ValidateException e1) {
        	  // TODO Auto-generated catch block
        	  e1.printStackTrace();
          } catch (DBOperationsException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
		response.setContentType("text/xml");
		try{
            response.setCharacterEncoding("UTF-8");
            out=response.getWriter();
        }catch (IOException e){
        }
		
        logger.info("Respuesta Usuario: "+respuesta);
        logger.info("---");
        
		out.write(respuesta);
	}
	
	static private Usuario validaPeticion(String xml) throws ValidateException{
        try {
        	Usuario usuario;
            ByteArrayInputStream input = new ByteArrayInputStream(xml.getBytes("UTF-8")); 
                
            // Prepara el schema a validar
		    Source source = new StreamSource(ServletUsuario.class.getResourceAsStream ("/XMLUsuario.xsd"));
            SchemaFactory schemaFactory = SchemaFactory.newInstance(javax.xml.XMLConstants.W3C_XML_SCHEMA_NS_URI);
            Schema schema = schemaFactory.newSchema(source);
                           
            // Crea el Unmarshaller
		    JAXBContext jaxbContext = JAXBContext.newInstance("com.kaizolo.gilabert.admin.XMLUsuario");
            Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
            unmarshaller.setSchema(schema);			
            usuario = ( Usuario )unmarshaller.unmarshal(input);			
                
            return usuario;
        }
        catch (JAXBException e){
                throw new ValidateException(e.getMessage());
        }
        catch (NullPointerException e){
                throw new ValidateException(e.getMessage());
        } 
        catch (UnsupportedEncodingException e) {
                throw new ValidateException(e.getMessage());
        }
        catch (SAXException e){
                e.printStackTrace();
                throw new ValidateException(e.getMessage());
        }
        }

}
